<?php

/**
 * @author dr4g0n
 * @copyright 2008
 */

require_once __DIR__."/../cache/cache.php";
require_once __DIR__."/../error-log/error.php";

/*
 * SQL class
 */

class sql {

    protected $dbhandle = NULL;
    protected $sqlcache = NULL;
    protected $sqlcaching = false;
    protected $sqlcaching_ttl = 60;
    public $lasterror;
    public $lastsql;
    protected $sql_queries_counter = 0;
    protected $method = "mysql";
    public $rows_affected = 0;
    protected $persistent = false;

    /**
     * Construct sql class, open new cache instance
     *  
     * @param string $path
     * path for cache files, default "cache/" 
     */
    public function __construct($path = "cache/") {
        $this->sqlcache = new SQLCache;
        $this->sqlcache->set_path($path);
    }

    /**
     * Turn on/off the sql caching
     * (so some queries can be cached and some don't')
     * Also sets ttl for cache files
     * 
     * @param string $caching
     * Turns on/off sql caching
     * @param string $ttl
     * Time to live for the cache, default 60
     * @return nothing  
     *   
     */
    public function sqlcaching($caching = false, $ttl = 60) {
        $this->sqlcaching = $caching;
        $this->sqlcaching_ttl = $ttl;
    }

    /**
     * Turn on/off the persistent connections
     * 
     * @param string $persistent
     * Rurn on or off persistent connections (default false)
     * @return none
     *   
     */
    public function persistent($persistent = false) {
        $this->persistent = $persistent;
    }

    /**
     * Gets sql query counter (how much queries executed)
     *
     * @return number of queries       
     *
     */
    public function get_counter() {
        return $this->sql_queries_counter;
    }

}

/*
 * MySQL class, extended from SQL class
 */

class mysql extends sql {

    /**
     * Destructor - close opened mysql handles
     * 
     * @return none
     */
    public function __destruct() {
        // No handle, just exit
        if ($this->dbhandle)
                return;
        if ($this->method == "mysqli")
            @mysqli_close($this->$dbhandle);    // Close connection if method is mysqli
        else
            @mysql_close($this->$dbhandle);     // Close connection if method is mysql
    }

    /**
     * Connect to MySQL server
     * 
     * @param string $hostname
     * MySQL server hostname
     * @param string $username
     * Username for server access
     * @param string $password
     * Password for server access
     * @param string $db
     * Database name
     * @param string $caching
     * Turns on/off caching, can also be set with sqlcaching
     * @param string $method
     * MySQL or MySQLi method, use MySQLi where possible
     * @param string $extras
     * Additional connection settings
     * @return true if succeed, if not, returns false and lasterror var is the error
     *   
     */
    public function connect($hostname = 'localhost', $username = 'root', $password = '', $db = "test", $caching = false, $method = "mysql", $extras = "") {
        if ($method == "mysqli")
            $this->method = "mysqli"; else
            $this->method = "mysql";
        if ($this->persistent)
            $this->method = "persistent";
        switch ($this->method) {
            case "mysql":
                $this->dbhandle = @mysql_connect($hostname, $username, $password, true);
                if (!@mysql_select_db($db, $this->dbhandle)) {
                    $this->lasterror = "Cannot select DB!";
                    return false;
                }
                break;
            case "mysqli":
                $this->dbhandle = @mysqli_connect($hostname, $username, $password, $db);
                if (!@mysqli_select_db($this->dbhandle, $db)) {
                    $this->lasterror = "Cannot select DB!";
                    return false;
                }
                break;
            case "persistent":
                $this->dbhandle = @mysql_pconnect($hostname, $username, $password);
                if (!@mysql_select_db($db, $this->dbhandle)) {
                    $this->lasterror = "Cannot select DB!";
                    return false;
                }
                break;
            default:
                $this->dbhandle = @mysql_connect($hostname, $username, $password, true);
                if (!@mysql_select_db($db, $this->dbhandle)) {
                    $this->lasterror = "Cannot select DB!";
                    return false;
                }
        }

        if (!$this->dbhandle) {
            $this->lasterror = "Cannot connect to DB!";
            return false;
        }
        $this->sqlcaching = $caching;
        return true;
    }

    /**
     * Connect to MySQL server using array
     * 
     * @param array $connect_array
     * Array of params used to connect
     * @param string $connect_array['hostname']
     * MySQL Server Hostname or IP address
     * @param string $connect_array['username']
     * Username for server access
     * @param string $connect_array['password']
     * Password for server access
     * @param string $connect_array['db']
     * Database name
     * @param string $connect_array['caching']
     * Turns on/off caching, can also be set with sqlcaching
     * @param string $connect_array['method']
     * MySQL or MySQLi method
     * @param string $connect_array['extras']
     * Additional connection settings
     * @return true if succeed, if not, returns false and lasterror var is the error       
     *   
     */
    public function connect_array(array $connect_array) {
        array_change_key_case($connect_array, LOCASE);
        return $this->connect($connect_array['hostname'], $connect_array['username'], $connect_array['password'], $connect_array['db'], $connect_array['caching'], $connect_array['method'], $connect_array['extras']);
    }

    /**
     * Create database
     * 
     * @param string $database_name
     * Name of the database to create
     * @return true if succeed, false if not       
     *   
     */
    public function create_database($database_name) {
        return mysql_create_db($database_name);
    }

    /**
     * Create table
     * 
     * @param string $table
     * Table name
     * @param string $definitions
     * Table definition array
     * @param string $engine
     * Engine (default myisam)
     * @param string $row_format
     * Format of the db rows (default dynamic)   
     * @return true if succeed, if not, returns false and lasterror var is the error       
     *   
     */
    public function create_table($table, array $definitions = array(), $engine = "myisam", $row_format = "dynamic") {
        if (empty($definitions) || empty($table))
            return false;
        $sql = "CREATE TABLE `{$table}` (";
        foreach ($definitions as $var => $def)
            $sql.="$var $def,";
        $sql = substr($sql, 0, strlen($sql) - 1);
        $sql.=") engine={$engine}, row_format={$row_format}";
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($this->dbhandle, $sql);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->sql_queries_counter++;
        return true;
    }

    /**
     * Delete database
     * 
     * @param string $database 
     * Database name
     * @return true if succeed       
     *   
     */
    public function delete_database($tablename) {
        return @mysql_drop_db($tablename);
    }

    /**
     * Delete table (DROP)
     * 
     * @param string $tablename
     * Table name
     * @return true if succeed, if not, returns false and lasterror var is the error       
     *   
     */
    public function delete_table($tablename) {
        if (!empty($tablename)) {
            $sql = "DROP TABLE `{$tablename}`";
            $this->lastsql = $sql;
            if ($this->method == "mysqli")
                $res = @mysqli_query($sql, $this->dbhandle);
            else
                $res = @mysql_query($sql, $this->dbhandle);
            $this->sql_queries_counter++;
            return true;
        } else
            return false;
    }

    /**
     * Clear table (TRUNCATE)
     * 
     * @param string $tablename 
     * Table name
     * @return true if succeed, if not, returns false and lasterror var is the error       
     *   
     */
    public function clear_table($tablename) {
        if (!empty($tablename)) {
            $sql = "TRUNCATE TABLE `{$tablename}`";
            $this->lastsql = $sql;
            if ($this->method == "mysqli")
                $res = @mysqli_query($sql, $this->dbhandle);
            else
                $res = @mysql_query($sql, $this->dbhandle);
            $this->sql_queries_counter++;
            return true;
        } else
            return false;
    }

    /**
     * Get one row (SELECT ... LIMIT 1)
     * 
     * @param string $table
     * Table name
     * @param string $find
     * Array of the rows to find
     * @param string $extra
     * Additions to the query
     * @return array of row if succeed, if not returns false
     *   
     */
    public function get_row($table, $find = array(), $extra = '') {
        $sql = "SELECT * from `{$table}`";
        if (!empty($find)) {
            $sql.=" WHERE ";
            foreach ($find as $var => $value) {
                //no variable? shouldn't happen - return with error
                if (empty($var)) {
                    $this->lasterror = 'find array misformed';
                    return false;
                }
                $sql.="`{$var}` = '{$value}' AND";
            }
            $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        }
        $sql.=" " . $extra;
        $sql.=" LIMIT 1";
        if ($this->sqlcaching && $cache = $this->sqlcache->get_cached_query($sql, $this->sqlcaching_ttl))
            return $cache;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        if (!$res) {
            $this->lasterror = 'Query returned null';
            return false;
        }
        $row = array();
        if ($this->method == "mysqli")
            $row = @mysqli_fetch_assoc($res);
        else
            $row = @mysql_fetch_assoc($res);
        $this->sql_queries_counter++;
        if ($this->sqlcaching)
            $this->sqlcache->cache_query($sql, $rows, $this->sqlcaching_ttl);
        return $row;
    }

    /**
     * Get rows (SELECT)
     * 
     * @param string $table
     * Table name
     * @param string $find
     * Array of the rows to find
     * @param string $extra
     * Additions to the query  
     * @return array of rows if succeed, if not returns false       
     *   
     */
    public function get_rows($table, $find = array(), $extra = '') {
        $sql = "SELECT * from `{$table}` ";
        if (!empty($find)) {
            $sql.=" WHERE ";
            foreach ($find as $var => $value) {
                //no variable? shouldn't happen - revert to original query
                if (empty($var)) {
                    $this->lasterror = 'find array malformed';
                    return false;
                }
                $sql.="`$var` = '$value' AND";
            }
            $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        }
        $sql.=" " . $extra;
        if ($this->sqlcaching && $cache = $this->sqlcache->get_cached_query($sql, $this->sqlcaching_ttl))
            return $cache;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        if (!$res) {
            $this->lasterror = 'Query returned null';
            return false;
        }
        $rows = array();
        while ($row = @mysql_fetch_assoc($res))
            $rows[] = $row;
        $this->sql_queries_counter++;
        if ($this->sqlcaching)
            $this->sqlcache->cache_query($sql, $rows, $this->sqlcaching_ttl);
        return $rows;
    }

    /**
     * Get like row (rows that have similiar match)
     * 
     * @param string $table
     * Table name
     * @param array $like
     * Array of the rows to find
     * @param string $extra
     * Extra query 
     * @return array of row if succeed, if not returns false       
     *   
     */
    public function get_likerows($table, $like = array(), $extra = "") {
        $sql = "SELECT * from `{$table}` ";
        if (empty($like))
            return false;
        $sql.=" WHERE ";
        foreach ($like as $var => $value) {
            //no variable? shouldn't happen - revert to original query
            if (empty($var)) {
                $this->lasterror = 'find array misformed';
                return false;
            }
            $sql.="`{$var}` LIKE '{$value}' AND ";
        }
        $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        if (!empty($extra))
            $sql.=" " . $extra;
        if ($this->sqlcaching && $cache = $this->sqlcache->get_cached_query($sql, $this->sqlcaching_ttl))
            return $cache;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        if (!$res) {
            $this->lasterror = 'Query returned null';
            return false;
        }
        $rows = array();
        while ($row = @mysql_fetch_assoc($res)) {
            $rows[] = $row;
        }
        $this->sql_queries_counter++;
        if ($this->sqlcaching)
            $this->sqlcache->cache_query($sql, $rows, $this->sqlcaching_ttl);
        return $rows;
    }

    /**
     * Simple raw MySQL query call
     * 
     * @param string $query
     * Table name
     * @return array of row if succeed, if not returns false       
     * 
     */
    public function sql($query) {
        if (empty($query))
            return false;
        if ($this->sqlcaching && $cache = $this->sqlcache->get_cached_query($sql, $this->sqlcaching_ttl))
            return $cache;
        $this->lastsql = $query;
        if ($this->method == "mysqli")
            $res = mysqli_query($query, $this->dbhandle);
        else
            $res = mysql_query($query, $this->dbhandle);
        if (!$res)
            return array();
        $rows = array();
        while ($row = @mysql_fetch_assoc($res)) {
            $rows[] = $row;
        }
        $this->sql_queries_counter++;
        if ($this->sqlcaching)
            $this->sqlcache->cache_query($sql, $rows, $this->sqlcaching_ttl);
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        return $rows;
    }

    /**
     * Delete row from the base (DELETE ... LIMIT1)
     * 
     * @param string $table
     * Table name
     * @param array $find
     * What to find, arrays
     * @param string $extra
     * Extra addition to query  
     * @return array of row if succeed, if not returns false       
     *   
     */
    public function delete_row($table, $find = array(), $extra = '') {
        $sql = "DELETE FROM `{$table}` ";
        if (!empty($find)) {
            $sql.=" WHERE ";
            foreach ($find as $var => $value) {
                //no variable? shouldn't happen - revert to original query
                if (empty($var)) {
                    $this->lasterror = 'find array misformed';
                    return false;
                }
                $sql.="`$var` = '$value' AND";
            }
            $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        }
        $sql.=" LIMIT 1";
        $sql.=" " . $extra;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->sql_queries_counter++;
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        return $row;
    }

    /**
     * Delete rows from the base (DELETE)
     * 
     * @param string $table
     * Table name
     * @param array $find
     * What to find, arrays
     * @param string $extra
     * Extra addition to query  
     * @return array of rows if succeed, if not returns false       
     *   
     */
    public function delete_rows($table, $find = array(), $extra = '') {
        $sql = "DELETE FROM `{$table}` ";
        if (!empty($find)) {
            $sql.=" WHERE ";
            foreach ($find as $var => $value) {
                //no variable? shouldn't happen - revert to original query
                if (empty($var)) {
                    $this->lasterror = 'find array misformed';
                    return false;
                }
                $sql.="`{$var}` = '{$value}' AND";
            }
            $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        }
        $sql.=" " . $extra;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->sql_queries_counter++;
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        return $row;
    }

    /**
     * Change rows in the base (UPDATE)
     * 
     * @param string $table
     * Table name
     * @param array $set
     * Array of values to set
     * @param array $where
     * Array of rows to match the change  
     * @return true if succeed, if not returns false       
     *   
     */
    public function change_rows($table, array $set, array $where = array(), $extra = '') {
        $sql = "UPDATE `{$table}` SET ";
        if (!is_array($set) || empty($set))
            return false;
        foreach ($set as $var => $value) {
            $sql.="`{$var}` = '{$value}', ";
        }
        $sql = substr($sql, 0, strlen($sql) - 2); //cut last AND
        if (!empty($where)) {
            $sql.=" WHERE ";
            foreach ($where as $var => $value) {
                //no variable? shouldn't happen - revert to original query
                if (empty($var)) {
                    $this->lasterror = 'find array malformed';
                    return false;
                }
                $sql.="`{$var}` = '{$value}' AND";
            }
            $sql = substr($sql, 0, strlen($sql) - 4); //cut last AND
        }
        $sql.=" " . $extra;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->sql_queries_counter++;
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        return true;
    }

    /**
     * Insert row in the base (INSERT)
     * 
     * @param string $table
     * Table name
     * @param array $row
     * Array of values to insert
     * @return true if succeed, if not returns false       
     *   
     */
    public function insert_row($table, $row) {
        if (!is_array($row))
            return false;
        $vars = '';
        $values = '';
        foreach ($row as $var => $value) {
            $vars.="`$var`,";
            if (strstr($value, "()"))   // if it has () it's function, not real value
                $values.="{$value},"; else
                $values.="'{$value}',";
        }
        $vars = substr($vars, 0, strlen($vars) - 1);
        $values = substr($values, 0, strlen($values) - 1);
        $sql = "INSERT INTO `{$table}` ({$vars}) values ({$values})";   // build sql query
        $sql.=" " . $extra;
        $this->lastsql = $sql;
        if ($this->method == "mysqli")
            $res = @mysqli_query($this->dbhandle, $sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->lastsql = $sql;
        $this->sql_queries_counter++;
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        if (!$res)
            $this->lasterror = "insert query failed, {$this->rows_affected} rows affected";
        return $res;
    }

    /**
     * Insert packed rows in the base (INSERT)
     * 
     * @param string $table
     * Table name
     * @param array $vars
     * Vars to insert
     * @param string $rows
     * Array of values to insert
     * @return true if succeed, if not returns false       
     *   
     */
    public function insert_rows($table, array $vars, array $values) {
        if (empty($table) || empty($values) || empty($vars))
            return false;
        $insert = "insert into `{$table}` (";
        foreach ($vars as $var)
            $insert.="`{$var}`,";
        $insert = substr($insert, 0, strlen($insert) - 1);
        $insert.=") values ";
        foreach ($values as $values_packed) {
            $insert.="(";
            foreach ($values_packed as $value)
                $insert.="\"{$value}\",";
            $insert = substr($insert, 0, strlen($insert) - 1);
            $insert.="),";
        }
        $insert = substr($insert, 0, strlen($insert) - 1);
        $insert.=" " . $extra;
        if ($this->method == "mysqli")
            $res = @mysqli_query($this->dbhandle, $insert, $this->dbhandle);
        else
            $res = @mysql_query($insert, $this->dbhandle);
        $this->lastsql = $insert;
        $this->sql_queries_counter++;
        $this->rows_affected = @mysql_affected_rows($this->dbhandle);
        if (!$res)
            $this->lasterror = "insert query failed, {$this->rows_affected} rows affected";
        return $res;
    }

    /**
     * Available tables informations (SHOW TABLE STATUS)
     * 
     * @return Tables with their info       
     *   
     */
    public function get_tables() {
        $sql = "SHOW TABLE STATUS";
        if ($this->method == "mysqli")
            $res = @mysqli_query($this->dbhandle, $sql, $this->dbhandle);
        else
            $res = @mysql_query($sql, $this->dbhandle);
        $this->lastsql = $sql;
        $this->sql_queries_counter++;
        $rows = array();
        while ($row = @mysql_fetch_assoc($res))
            $rows[] = $row;
        $this->sql_queries_counter++;
        if ($this->sqlcaching)
            $this->sqlcache->cache_query($sql, $rows, $this->sqlcaching_ttl);
        return $rows;
    }

    /**
     * Last id of the row after INSERT
     * 
     * @return Inserted id
     *   
     */
    public function get_insert_id() {
        $res = @mysql_insert_id($this->dbhandle);
        return $res;
    }

}

/*
 *  POSTGRESQL class
 */

class postgresql extends sql {
    
}

/*
 *  MSSQL class
 */

class mssql extends sql {
    
}

?>